from fastapi import APIRouter, Depends, Query
from sqlalchemy.orm import Session
from typing import Optional,List
from datetime import date
from app.db.database import get_db
from app.services import scholar_service
from app.utilities.jwt_token import decode_token
from app.utilities.error import Error
from fastapi.security import OAuth2PasswordBearer
from app.schema.dashboard_manual_schema import ITEDashboardManualUpdate
from fastapi.responses import StreamingResponse
import pandas as pd
import io
from typing import Optional, List
from app.repositories.scholar_report_repo import ScholarReportRepo
from fastapi import HTTPException
router = APIRouter(prefix="/scholars", tags=["scholars"])


oauth2_scheme = OAuth2PasswordBearer(tokenUrl="token")


@router.get("/full")
def get_full_scholars(
    skip: int = Query(0, ge=0),
    limit: int = Query(20, le=100),
    token: str = Depends(oauth2_scheme),
    db: Session = Depends(get_db)
):

    if not token:
        raise Error.unauthorized("Missing token")

    try:
        payload = decode_token(token)
    except Exception as e:
        raise Error.unauthorized(f"Invalid token: {str(e)}")

    user_id = payload.get("sub")
    if not user_id:
        raise Error.unauthorized("Invalid token: no subject")


    return scholar_service.list_scholar_full_paginated(db, skip, limit)


@router.get("/dashboard")
def get_scholar_dashboard(
    skip: int = Query(0, ge=0),
    limit: int = Query(100, le=500),
    token: str = Depends(oauth2_scheme),
    db: Session = Depends(get_db)
):
    if not token:
        raise Error.unauthorized("Missing token")

    try:
        payload = decode_token(token)
    except Exception as e:
        raise Error.unauthorized(f"Invalid token: {str(e)}")

    user_id = payload.get("sub")
    if not user_id:
        raise Error.unauthorized("Invalid token: no subject")

    return scholar_service.list_scholar_dashboard(db=db, skip=skip, limit=limit)

@router.get("/full/search")
def search_full_scholars(
    skip: int = Query(0, ge=0),
    limit: int = Query(20, le=100),
    keywords: Optional[str] = Query(None, description="Fuzzy keyword for filtering, e.g. name, student ID, or email"),
    start_date_input: Optional[date] = Query(
                None,
                description="Filter ITEs whose ite_start/ite_finish fall in this period (YYYY-MM-DD)"
            ),
        end_date_input: Optional[date] = Query(
                None,
                description="Filter ITEs whose ite_start/ite_finish fall in this period (YYYY-MM-DD)"
        ),
    token: str = Depends(oauth2_scheme),
    db: Session = Depends(get_db)
):
    if not token:
        raise Error.unauthorized("Missing token")

    try:
        payload = decode_token(token)
    except Exception as e:
        raise Error.unauthorized(f"Invalid token: {str(e)}")

    user_id = payload.get("sub")
    if not user_id:
        raise Error.unauthorized("Invalid token: no subject")

    return scholar_service.list_scholar_full_paginated_with_search(
        db=db,
        skip=skip,
        limit=limit,
        keywords=keywords,
        start_date_input=start_date_input,
        end_date_input=end_date_input
    )

@router.get("/dashboard/search")
def search_full_scholars(
    skip: int = Query(0, ge=0),
    limit: int = Query(20, le=100),
    keywords: Optional[str] = Query(None, description="Fuzzy keyword for filtering, e.g. name, student ID, or email"),

    token: str = Depends(oauth2_scheme),
    db: Session = Depends(get_db)
):
    if not token:
        raise Error.unauthorized("Missing token")

    try:
        payload = decode_token(token)
    except Exception as e:
        raise Error.unauthorized(f"Invalid token: {str(e)}")

    user_id = payload.get("sub")
    if not user_id:
        raise Error.unauthorized("Invalid token: no subject")

    return scholar_service.list_scholar_dashboard_with_search(
        db=db,
        skip=skip,
        limit=limit,
        keywords=keywords,

    )

@router.get("/ite-dashboard-manual")
def get_ite_dashboard_manual(skip: int = Query(0, ge=0),limit: int = Query(20, le=100),db: Session = Depends(get_db)
                             , token: str = Depends(oauth2_scheme)
                             ):

    if not token:
        raise Error.unauthorized("Missing token")

    try:
        payload = decode_token(token)
    except Exception as e:
        raise Error.unauthorized(f"Invalid token: {str(e)}")

    user_id = payload.get("sub")
    if not user_id:
        raise Error.unauthorized("Invalid token: no subject")
    return scholar_service.list_ite_dashboard_manual_paginated(db, skip, limit)

@router.put("/batch-update")
def batch_update_dashboard(
    updates: List[ITEDashboardManualUpdate],
    token: str = Depends(oauth2_scheme),
    db: Session = Depends(get_db)
):
    if not token:
        raise Error.unauthorized("Missing token")

    try:
        payload = decode_token(token)
    except Exception as e:
        raise Error.unauthorized(f"Invalid token: {str(e)}")

    user_id = payload.get("sub")
    if not user_id:
        raise Error.unauthorized("Invalid token: no subject")
    scholar_service.update_ite_dashboard_manual_batch(db, updates)
    return {"message": "Batch update successful", "count": len(updates)}

@router.get("/export-performance-report", summary="Export Scholar Performance Summary Report")
def export_scholar_performance_report(
    query: str = Query(..., description="COCODE or Umbrella Code"),
    db: Session = Depends(get_db)
    ):
    repo = ScholarReportRepo(db)
    cocodes, is_umbrella = repo.get_cocodes_from_query(query)

    if not cocodes:
        raise HTTPException(status_code=404, detail=f"No cocodes found for query '{query}'")

    data = repo.get_performance_report_records(cocodes=cocodes)

    if not data:
        raise HTTPException(status_code=404, detail=f"No records found for query '{query}'")

    df = pd.DataFrame(data)
    if is_umbrella:
        if "COCODE" in df.columns:
            cols = ["COCODE"] + [col for col in df.columns if col != "COCODE"]
            df = df[cols]
    else:
        if "COCODE" in df.columns:
            df.drop(columns=["COCODE"], inplace=True)

    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name="ScholarPerformance")
    output.seek(0)

    filename = f"scholar_performance_report_{query}.xlsx"
    headers = {
    "Content-Disposition": f"attachment; filename={filename}"
    }
    return StreamingResponse(
        output,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers=headers
    )